import tushare as ts
import pandas as pd
# import datetime
import time
import matplotlib.pyplot as plt
import os
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
import xlwings as xw
# import threading
import multiprocessing

# from queue import Queue
# from threading import Lock, RLock

os.chdir(r'C:\Users\***\Desktop\2020\03_Jupyter笔记')
# q = Queue()
token = '***'
pro = ts.pro_api(token)


def tedliu(ts_code_p):
    # os.chdir(r'C:\Users\'***'\Desktop\2020\03_Jupyter笔记')
    df = pro.daily(ts_code=ts_code_p, start_date='20200701', end_date='20200801', fields='ts_code,trade_date,close')
    df.replace("202007", "", regex=True, inplace=True)
    df = df.T
    df.columns = df.iloc[1]
    df.drop("trade_date", inplace=True)
    df.rename(index={"close": ts_code_p}, inplace=True)
    df.drop("ts_code", inplace=True)
    a = df.columns.values.tolist()
    b = df.loc[ts_code_p]
    plt.rcParams['font.sans-serif'] = ['SimHei']
    plt.rcParams['axes.unicode_minus'] = False
    plt.xlabel('Daily')
    plt.ylabel('Value')
    plt.plot(a, b)
    plt.savefig((str(ts_code_p) + '.png'), dpi=100)
    plt.close()
    # plt.show()
    # print(df)
    return df


# def tedliu(alist):
#     lst_test = []
#     for ts_code_p in alist:
#         df = pro.daily(ts_code=ts_code_p, start_date='20200701', end_date='20200801', fields='ts_code,trade_date,close')
#         df.replace("202007", "", regex=True, inplace=True)
#         df = df.T
#         df.columns = df.iloc[1]
#         df.drop("trade_date", inplace=True)
#         df.rename(index={"close": ts_code_p}, inplace=True)
#         df.drop("ts_code", inplace=True)
#         a = df.columns.values.tolist()
#         b = df.loc[ts_code_p]
#         plt.xlabel('Daily')
#         plt.ylabel('Value')
#         plt.plot(a, b)
#         plt.savefig((str(ts_code_p) + '.png'), dpi=100)
#         plt.close()
#         # qpara.put(df)
#         lst_test.append(df)
#     return lst_test


if __name__ == "__main__":
    print("...Starting...")
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    print(now)
    now1 = time.time()
    # os.chdir(r'C:\Users\l00485378\Desktop\2020\03_Jupyter笔记')
    # q = Queue()
    # %config InlineBackend.figure_formats=['svg']
    # token = '8b334e2d5d482292aa8b516c7f59dd6d23c0266dac3dc407dfb63ea1'
    # pro = ts.pro_api(token)
    # firstDay = "20200701"
    # today = datetime.date.today().strftime('%Y%m%d')
    data = pro.query('stock_basic', exchange='', list_status='L', market="主板", fields='ts_code,name')
    # lstdata = data['ts_code'].values.tolist()
    # lstdata_cf = [lstdata[i:i + 400] for i in range(0, len(lstdata), 400)]
    # for _ in ['000001.SZ', '000002.SZ', '000004.SZ', '000005.SZ']:

    # threads = []
    # for _ in range(5):
    #     # t = threading .Thread(target=tedliu, args=(lstdata_cf[_], q))
    #     t = multiprocessing.Process(target=tedliu, args=(lstdata_cf[_], q))
    #     t.start()
    #     threads.append(t)

    pool = multiprocessing.Pool(processes=6)

    # lst = []
    # for i in data['ts_code']:
    #     # for i in ['000001.SZ', '000002.SZ', '000004.SZ', '000005.SZ']:
    #     # print(_)
    #     res = pool.apply_async(tedliu, args=(i,))
    #     lst.append(res.get())
    #     # print(res.get())

    multiple_results = [pool.apply_async(tedliu, args=(i,)) for i in data['ts_code']]
    # multiple_results = [pool.apply_async(tedliu, args=(i,)) for i in ['000001.SZ', '000002.SZ', '000004.SZ', '000005.SZ']]
    lst = [res.get() for res in multiple_results]

    pool.close()
    pool.join()

    # for thread in threads:
    #     thread.join()
    #
    # lst = []
    # for _ in range(1984):
    #     lst.append(q.get())
    #     print(_)
    #
    # # print("lst is ", lst)
    # # for _ in data['ts_code']:
    # #     df0 = tedliu(_)
    # #     lst.append(df0)
    #
    # print(lst)
    result = pd.concat(lst)
    result.insert(0, "股票名字", data['name'])
    result.to_excel('phase1.xlsx')
    # result["股票名字"] = data['name']
    wb = load_workbook('phase1.xlsx')
    ws = wb.active

    for i in range(2, len(lst) + 2):
        ws.column_dimensions['Y'].width = 65
        ws.row_dimensions[i].height = 120
        img = Image(str(ws.cell(row=i, column=1).value) + '.png')  # 缩放图片
        img.width, img.height = (500, 154)
        ws.add_image(img, ("Y" + str(i)))  # 图片 插入 A1 的位置上

    wb.save('phase1.xlsx')  # 新的结果保存输出

    # 调整格式
    wb = xw.Book('phase1.xlsx')
    sht = wb.sheets("Sheet1")
    nrow = sht.api.UsedRange.Rows.count
    ncol = sht.api.UsedRange.Columns.count
    rng = sht[0:nrow, 0:ncol + 1]
    rng.api.HorizontalAlignment = -4108
    rng.api.VerticalAlignment = -4108
    rng.api.Font.Name = "Biome"
    rng.api.Font.Bold = True
    rng.column_width = 5.5

    arng = sht[0:nrow, 0]
    arng.column_width = 12

    for i in [9, 11, 12]:
        rng.api.Borders(i).LineStyle = 2
        rng.api.Borders(i).Weight = 2

    wb.save('phase1.xlsx')
    print("------------------------------------")
    print("...Finished...")
    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    print(now)
    now2 = time.time()
    now3 = now2 - now1
    print("...用时{} Secs...".format(round(now3, 2)))